package com.program.facesigninsystem.util;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ExcelUtil {
    private static Logger log = Logger.getLogger(ExcelUtil.class.toString());
    /**
     * 用户信息导出类
     * @param response 响应
     * @param fileName 文件名
     * @param columnList 每列的标题名
     * @param dataList 导出的数据
     */
    public static void uploadExcelAboutUser(HttpServletRequest request,HttpServletResponse response, String fileName, List<String> columnList, List<Map<String,Object>> dataList){
        //声明输出流
        OutputStream os = null;
        //设置响应头
        setResponseHeader(request,response,fileName);
        try {
            //获取输出流
            os = response.getOutputStream();
            //内存中保留1000条数据，以免内存溢出，其余写入硬盘
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);
            //获取该工作区的第一个sheet
            Sheet sheet1 = wb.createSheet("sheet1");
            int excelRow = 0;
            //创建标题行
            Row titleRow = sheet1.createRow(excelRow++);
            for(int i = 0;i<columnList.size();i++){
                //创建该行下的每一列，并写入标题数据
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(columnList.get(i));
            }
            //设置内容行
            if(dataList!=null && dataList.size()>0){
                //序号是从1开始的
                int count = 1;
                //外层for循环创建行
                for (Map<String, Object> stringObjectMap : dataList) {
                    Row dataRow = sheet1.createRow(excelRow++);
                    //内层for循环创建每行对应的列，并赋值
                    for (int j = -1; j < dataList.get(0).size(); j++) {//由于多了一列序号列所以内层循环从-1开始
                        Cell cell = dataRow.createCell(j + 1);
                        if (j == -1) {//第一列是序号列，不是在数据库中读取的数据，因此手动递增赋值
                            cell.setCellValue(count++);
                        } else {//其余列是数据列，将数据库中读取到的数据依次赋值
                            Object o = null;
                            if (j == 5) {
                                o = stringObjectMap.get("academy");
                            }
                            if (j == 4) {
                                o = stringObjectMap.get("major");
                            }
                            if (j == 3) {
                                o = stringObjectMap.get("class");
                            }
                            if (j == 0) {
                                o = stringObjectMap.get("name");
                            }
                            if (j == 1) {
                                o = stringObjectMap.get("state");
                            }
                            if (j == 2) {
                                o = stringObjectMap.get("signintime");
                            }
                            if (o == null) {
                                cell.setCellValue("");
                            } else {
                                cell.setCellValue(o.toString());
                            }
                        }
                    }
                }
            }
            //将整理好的excel数据写入流中
            wb.write(os);
        } catch (IOException e) {
            log.log(Level.SEVERE,"Error parsing date",e);
        } finally {
            try {
                // 关闭输出流
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                log.log(Level.SEVERE,"Error parsing date",e);
            }
        }
    }

    /*
        设置浏览器下载响应头
     */
    private static void setResponseHeader(HttpServletRequest request,HttpServletResponse response, String fileName) {
        try {
            if ("IE".equals(getBrowser(request))) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            }else{
                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            log.log(Level.SEVERE,"Error parsing date",ex);
        }
    }
    /**
     * 判断客户端浏览器类型
     * @param request
     * @return
     */
    private static String getBrowser(HttpServletRequest request) {
        String UserAgent = request.getHeader("User-Agent").toLowerCase();
        if (UserAgent.contains("firefox")){
            return "FF";
        }else if(UserAgent.contains("safari")){
            return "Chrome";
        }else{
            return "IE";
        }
    }

    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public static List readXls(File file) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                List<List> outerList= new ArrayList<>();
                // 每个页签创建一个Sheet对象
                jxl.Sheet sheet = wb.getSheet(index);
                int j, col=0;
                // sheet.getColumns()返回该页的总列数
                for (j = 0; j < sheet.getColumns(); j++){
                    String cellinfo = sheet.getCell(j, 0).getContents();
                    if("姓名".equals(cellinfo)){
                        col=j;
                        break;
                    }
                }
                if(j==sheet.getColumns()){
                    return null;
                }
                // sheet.getRows()返回该页的总行数
                for (int i = 1; i < sheet.getRows(); i++) {
                    List innerList=new ArrayList();
                    String cellinfo = sheet.getCell(col, i).getContents();
                    if(cellinfo.isEmpty()){
                        continue;
                    }
                    innerList.add(cellinfo);
                    System.out.print(cellinfo);
                    outerList.add(i-1, innerList);
                    System.out.println();
                }
                return outerList;
            }
        } catch (BiffException | IOException e) {
            log.log(Level.SEVERE,"Error parsing date",e);
        }
        return null;
    }

    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public static List readXlsx(File file) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            XSSFWorkbook wb = new XSSFWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                List<List> outerList= new ArrayList<>();
                // 每个页签创建一个Sheet对象
                XSSFSheet sheet = wb.getSheetAt(index);
                int j,col=0;
                for (j = 0; j < sheet.getRow(0).getPhysicalNumberOfCells(); j++){
                    XSSFRow row=sheet.getRow(0);
                    XSSFCell cell=row.getCell(j);
                    String cellinfo = String.valueOf(cell.getStringCellValue());
                    if("姓名".equals(cellinfo)){
                        col=j;
                        break;
                    }
                }
                if(j==sheet.getRow(0).getPhysicalNumberOfCells()){
                    return null;
                }
                // sheet.getRows()返回该页的总行数
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    List innerList=new ArrayList();
                    XSSFRow row=sheet.getRow(i);
                    // sheet.getColumns()返回该页的总列数
                    XSSFCell cell=row.getCell(col);
                    String cellinfo = String.valueOf(cell.getStringCellValue());
                    if(cellinfo.isEmpty()) {
                        continue;
                    }
                    innerList.add(cellinfo);
                    System.out.print(cellinfo);
                    outerList.add(i-1, innerList);
                    System.out.println();
                }
                return outerList;
            }
        } catch (IOException e) {
            log.log(Level.SEVERE,"Error parsing date",e);
        }
        return null;
    }
}
